﻿using System;
using System.Collections.Generic;
using System.Web;
using System.Data.SqlClient;
using System.Data;
using System.Collections;

namespace ORS.DAL
{
    public class DBHelper
    {
        public object GetRoleName(string sqlstr)
        {
            //ORS.DAL.mssqlopr db = new ORS.DAL.mssqlopr();
            try
            {
                mssqlopr.Conn.Open();
                SqlCommand Comm = new SqlCommand(sqlstr, mssqlopr.Conn);
                object result = Comm.ExecuteScalar();
                return result;
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                mssqlopr.Conn.Close();
            }
        }

        public DataSet getDateSet(string sqlstr)
        {
            //ORS.DAL.mssqlopr db = new ORS.DAL.mssqlopr();
            try
            {
                mssqlopr.Conn.Open();
                SqlCommand Comm = new SqlCommand(sqlstr, mssqlopr.Conn);
                SqlDataAdapter da = new SqlDataAdapter(Comm);
                DataSet ds = new DataSet();
                da.Fill(ds);
                return ds;
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                mssqlopr.Conn.Close();
            }
        }

        public void setDate(string sqlstr)
        {
            //ORS.DAL.mssqlopr db = new ORS.DAL.mssqlopr();
            try
            {
                mssqlopr.Conn.Open();
                SqlCommand Comm = new SqlCommand(sqlstr, mssqlopr.Conn);
                Comm.ExecuteNonQuery();
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                mssqlopr.Conn.Close();
            }
        }

        public bool ExecuteSQL(string SqlStrings)
        {
            bool success = true;
            //ORS.DAL.mssqlopr db = new ORS.DAL.mssqlopr();
            
            mssqlopr.Conn.Open();
            SqlCommand cmd = new SqlCommand();
            SqlTransaction trans = mssqlopr.Conn.BeginTransaction();
            cmd.Connection = mssqlopr.Conn;
            cmd.Transaction = trans;
            try
            {
                cmd.CommandText = SqlStrings;
                cmd.ExecuteNonQuery();
                trans.Commit();
            }
            catch
            {
                success = false;
                trans.Rollback();
            }
            finally
            {
                mssqlopr.Conn.Close();
            }
            return success;
        }

        public bool Insert(string TableName, Hashtable Cols)
        {
            int Count = 0;

            if (Cols.Count <= 0)
            {
                return true;
            }

            String Fields = " (";
            String Values = " Values(";
            foreach (DictionaryEntry item in Cols)
            {
                if (Count != 0)
                {
                    Fields += ",";
                    Values += ",";
                }
                Fields += item.Key.ToString();
                Values += item.Value.ToString();
                Count++;
            }
            Fields += ")";
            Values += ")";

            string SqlString = "Insert into " + TableName + Fields + Values;

            return Convert.ToBoolean(ExecuteSQL(SqlString));
        }
            
    }

}